/// <reference types="@vertx/core/runtime" />
// @ts-check

import {MySQLClient, MySQLPool} from "@vertx/mysql-client";
import {SqlConnection, Tuple} from "@vertx/sql-client";
import {PoolOptions} from "@vertx/sql-client/options";
import {MySQLConnectOptions} from "@vertx/mysql-client/options";
import {Promise} from "@vertx/core";

const headerName = "Content-Type";
const headerValue = "application/json";

const countBookSQL = "SELECT COUNT(*) as count FROM Book";
const queryBookLimitSQL = "SELECT id, name, price FROM Book LIMIT ?,?";
const insertBookSQL = "INSERT INTO Book (name, price) VALUES (?,?)";
const updateBookSQL = "UPDATE Book set name=?, price=? where id = ?";
const deleteBookSQL = "DELETE FROM Book where id = ?";

let mysqlOption = new MySQLConnectOptions()
  .setPort(3306)
  .setHost("localhost")
  .setDatabase("sweet")
  .setUser("root")
  .setPassword("123456");

const poolOptions = new PoolOptions().setMaxSize(5);

const mysqlClient = MySQLPool.pool(vertx, mysqlOption, poolOptions);

async function getConn() {
  let p = Promise.promise();
  mysqlClient.getConnection(p);
  return await p.future();
}

async function updateSQL(sql, params) {
  let conn = await getConn();
  try {
    let resultPromise = Promise.promise();
    conn.preparedQuery(sql, params, resultPromise);
    let result = await resultPromise.future();
    let id = result.property(MySQLClient.LAST_INSERTED_ID);
    return {
      "rowCount": result.rowCount(),
      "id": id
    };
  } finally {
    conn.close();
  }
}

function getSqlToColumns(querySql) {
  let startIndex = querySql.indexOf("T") + 1;
  let endIndex = querySql.indexOf("FROM");
  return querySql.substring(startIndex, endIndex).split(",").map(t => t.trim());

}

function convertRowSet(columnList, rows) {
  let result = [];
  rows.forEach(row => {
    let item = {};
    for (let i = 0; i < columnList.length; i++) {
      let v = row.getValue(i);
      item[columnList[i]] = v ? v.toString() : 'null';
    }
    result.push(item);
  });
  return result;
}

// 适合简单查询，无需手动关闭连接
async function simpleQuery(sql, params) {
  let p = Promise.promise();
  mysqlClient.preparedQuery(sql, params, p);
  let rows = await p.future();
  let columns = getSqlToColumns(sql);
  return convertRowSet(columns, rows);
}

async function getBookCount(conn) {
  let p = Promise.promise();
  conn.preparedQuery(countBookSQL, p);
  let result = await p.future();

  for (let item of result) {
    return item.getLong(0);
  }
}

function printRows(rows) {
  rows.forEach(row => {
    console.log(row);
  });
}

async function queryBookLimit(conn, start, length) {
  let p = Promise.promise();
  conn.preparedQuery(queryBookLimitSQL, Tuple.of(start, length), p);
  let rows = await p.future();
  // printRows(rows);
  let columns = getSqlToColumns(queryBookLimitSQL);
  return convertRowSet(columns, rows);
}

async function manyQuery(start, length) {
  console.log("start: " + start + ", len: " + length);
  let conn = await getConn();
  try {
    let count = await getBookCount(conn);
    console.log("count: " + count);
    let bookArray = await queryBookLimit(conn, start, length);
    return {
      "total": count,
      "data": bookArray
    };
  } finally {
    conn.close();
  }
}

module.exports = {
  // 处理权限等操作
  authHandler: (ctx) => {
    console.log("authHandler...");
    ctx.next();
  },

  helloHandler: (ctx) => {
    ctx.response().end("hello " + Date())
  },

  queryBook: async (ctx) => {
    const body = ctx.getBodyAsJson();
    console.log("query body: " + body);
    let list = await simpleQuery(queryBookLimitSQL,
      Tuple.of(body.getInteger("start"), body.getInteger("length")));
    ctx.response()
      .putHeader(headerName, headerValue)
      .end(JSON.stringify(list))
  },

  queryBook2: async (ctx) => {
    const body = ctx.getBodyAsJson();
    console.log("body: " + body);
    let result = await manyQuery(body.getInteger("start"), body.getInteger("length"));
    ctx.response()
      .putHeader(headerName, headerValue)
      .end(JSON.stringify(result));
  },

  createBook: async (ctx) => {
    const body = ctx.getBodyAsJson();
    console.log("body: " + body);
    let result = await updateSQL(insertBookSQL,
      Tuple.of(body.getString("name"), body.getDouble("price")));
    ctx.response()
      .putHeader(headerName, headerValue)
      .end(JSON.stringify(result));
  },

  updateBook: async (ctx) => {
    const body = ctx.getBodyAsJson();
    let id = body.getInteger("id");
    let result = await updateSQL(updateBookSQL, Tuple.of(body.getString("name"), body.getDouble("price"), id));
    ctx.response()
      .putHeader(headerName, headerValue)
      .end(JSON.stringify(result));
  },

  deleteBook: async (ctx) => {
    let id = ctx.request().getParam("id");
    let result = await updateSQL(deleteBookSQL, Tuple.of(id));
    ctx.response()
      .putHeader(headerName, headerValue)
      .end(JSON.stringify(result));
  },

};



